﻿using System;
using System.Collections.Generic;
using System.Text;
using NLBS.Entity;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;

namespace NLBS.Data
{
    public partial class AccessDataProvider : DbProvider
    {
        #region guestBook

        public override IList<GuestBook> GetGuestBook()
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT * FROM Guestbook");
            IList<GuestBook> lists = new List<GuestBook>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    GuestBook gutstBook = CommonDataProvider.PopulateGuestBookFromIDataReader(dataReader);
                    lists.Add(gutstBook);
                }
            }
            return lists;
        }

        public override IList<GuestBook> GetTopGuestBook()
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT TOP 10 gb_id, gb_username, gb_userID, gb_content, gb_editMark, gb_ubbFlags, gb_postTime, gb_replyUsername, gb_reply, gb_replyTime, gb_hidden, gb_ip FROM Guestbook");
            IList<GuestBook> lists = new List<GuestBook>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    GuestBook gutstBook = CommonDataProvider.PopulateGuestBookFromIDataReader(dataReader);
                    lists.Add(gutstBook);
                }
            }
            return lists;
        }

        public override GuestBook GetGuestBookByID(int gbookID)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT * FROM Guestbook WHERE (gb_id = ?)");
            db.AddInParameter(dbCommand, "gbookID", DbType.Int32, gbookID);
            IDataReader dr = db.ExecuteReader(dbCommand);
            GuestBook guestBook = null;
            if (dr.Read())
            {
                guestBook = CommonDataProvider.PopulateGuestBookFromIDataReader(dr);
            }
            return guestBook;
        }

        public override int InsertGuestBook(GuestBook gbook)
        {
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();
                try
                {
                    DbCommand dbCommand = db.GetSqlStringCommand("INSERT INTO Guestbook (gb_username, gb_userID, gb_content, gb_editMark, gb_ubbFlags, gb_postTime, gb_replyUsername, gb_reply, gb_replyTime, gb_hidden, gb_ip) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                    db.AddInParameter(dbCommand, "userName", DbType.String, gbook.UserName);
                    db.AddInParameter(dbCommand, "userID", DbType.Int32, gbook.UserID);
                    db.AddInParameter(dbCommand, "content", DbType.String, gbook.Content);
                    db.AddInParameter(dbCommand, "editMark", DbType.String, gbook.EditMark);
                    db.AddInParameter(dbCommand, "ubbFlags", DbType.String, gbook.UbbFlags);
                    db.AddInParameter(dbCommand, "postTime", DbType.DateTime, gbook.PostTime);
                    db.AddInParameter(dbCommand, "replyUserName", DbType.String, gbook.ReplyUserName);
                    db.AddInParameter(dbCommand, "reply", DbType.String, gbook.Reply);
                    db.AddInParameter(dbCommand, "replyTime", DbType.DateTime, gbook.ReplyTime);
                    db.AddInParameter(dbCommand, "hidden", DbType.Boolean, gbook.Hidden);
                    db.AddInParameter(dbCommand, "ip", DbType.String, gbook.IP);
                    db.ExecuteNonQuery(dbCommand,transaction);
                    dbCommand.Parameters.Clear();
                    dbCommand.CommandText = "SELECT @@IDENTITY";
                    int result = (int)db.ExecuteScalar(dbCommand, transaction);
                    transaction.Commit();
                    return result;
                }
                catch
                {
                    // Roll back the transaction. 
                    transaction.Rollback();
                }
                connection.Close();
            }
            return 0;
        }

        public override void UpdateGuestBook(GuestBook gbook)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("UPDATE  Guestbook SET gb_replyUsername = ?, gb_reply = ?, gb_replyTime = ? WHERE   (gb_id = ?)");
            db.AddInParameter(dbCommand, "replyUserName", DbType.String, gbook.ReplyUserName);
            db.AddInParameter(dbCommand, "reply", DbType.String, gbook.Reply);
            db.AddInParameter(dbCommand, "replyTime", DbType.DateTime, gbook.ReplyTime);
            db.AddInParameter(dbCommand, "gbookID", DbType.Int32, gbook.GuestBookID);
            db.ExecuteNonQuery(dbCommand);
        }

        public override void DeleteGuestBook(int gbookID)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("DELETE FROM `Guestbook` WHERE ((`gb_id` = ?))");
            db.AddInParameter(dbCommand, "gbookID", DbType.Int32, gbookID);
            db.ExecuteNonQuery(dbCommand);
        }

        public override int GetAllGuestBookCount()
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT COUNT(gbookID) FROM Guestbook");
            return Convert.ToInt32(db.ExecuteScalar(dbCommand));
        }

        public override IPagedCollection<GuestBook> GetAllGuestBookByPage(int startIndex, int pageSize)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("GetAllGuestBookByPage2000");
            db.AddInParameter(dbCommand, "startIndex", DbType.Int32, startIndex);
            db.AddInParameter(dbCommand, "pageSize", DbType.Int32, pageSize);

            IPagedCollection<GuestBook> lists = new PagedCollection<GuestBook>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    GuestBook gutstBook = CommonDataProvider.PopulateGuestBookFromIDataReader(dataReader);
                    lists.Add(gutstBook);
                }
                lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
            }
            return lists;
        }

        public override IPagedCollection<GuestBook> GetGuestBookBySearch(int startIndex, int pageSize, string prefix)
        {
            StringBuilder sb = new StringBuilder();
            string[] prefixArray = prefix.Split(new char[] { ' ' });

            for (int i = 0; i < prefixArray.Length; i++)
            {
                if (i > 0)
                {
                    sb.Append("AND ");
                }
                sb.AppendFormat("gb_content LIKE '%{0}%'", prefixArray[i]);
            }
            //comm_content
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT top {0} gb.*, 
            FROM [Guestbook] gb 
            WHERE gb_id<=(select min(gb_id) 
            from (select top {1} gb_id from Guestbook order by gb_id desc) as T) AND {2} 
            ORDER BY gb_postTime DESC;
            ", startIndex, pageSize, sb.ToString()));
            IDataReader dr = db.ExecuteReader(dbCommand);
            IPagedCollection<GuestBook> lists = new PagedCollection<GuestBook>();
            lists.MaxItems = ExecuteScalar(string.Format("SELECT COUNT(gb_id) AS TotalRecords from Guestbook where {2}", sb.ToString()));
            GuestBook gb = null;
            while (dr.Read())
            {
                gb = CommonDataProvider.PopulateGuestBookFromIDataReader(dr);
                //comment.Article = PopulateCommentArticleFromIDataReader(dr);
                lists.Add(gb);
            }
            return lists;
        }

        #endregion
    }
}
